IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id('MD3.ExecuteSQL'))
BEGIN
	DROP PROCEDURE MD3.ExecuteSQL
END
GO

CREATE PROCEDURE MD3.ExecuteSQL
	@SQL nvarchar(max)

	AS

BEGIN  
	
	/*
	
		This procedure executes all metadata-driven SQL statements in MD3.  If we have a central executor we can do interesting things like:
			--have a central logger of commands executed
			--create a WhatIf option on MD3
			--change the verbosity level of logging

		There are some basic rules that should be adhered to.  These aren't my rules, they are necessary due to goofiness with error handling in SQL
		Server.  It is difficult to RETHROW an error (prior to 2012) and it is difficult to catch the original ERROR_NUMBER() if you want to "handle" the error.

		--CASE 1 (Most common usage, fail on error)
			--this assumes you are not going to "handle" any errors that may arise, rather, you actually want a FAILURE.  
			--this logs 2 rows to MD3.Log (I, then E)
			--MD3 process sees the error and will FAIL
			--Message actually displayed to caller is Msg 50000 (but the actual ERROR_NUMBER() is in the error body)
		EXEC MD3.ExecuteSQL 
				@SQL = 'SELECT * FROM DoesNotExist'

		--CASE 2 (Ignore Errors)
			--there may be times you want to totally ignore an error.  This pattern will ignore the error, kinda.  MD3 will not fail, but the E is still logged in MD3.Log
			--ERROR_NUMBER in the caller will ALWAYS be 50000
			--this logs 2 rows to MD3.Log (I, then E)
			--MD3 process will NOT see the error and will NOT FAIL.  This is because the CATCH block did its job.  
		BEGIN TRY
			EXEC MD3.ExecuteSQL 
				@SQL = 'SELECT * FROM DoesNotExist'
		END TRY 
		BEGIN CATCH 
			SELECT ERROR_NUMBER() AS ErrorNumber
		END CATCH 

		--CASE 2 (Handle Errors)
			--there may be times we need to catch a given error number and handle it.  But due to goofiness mentioned above this becomes a little weird.  
			--ERROR_NUMBER in the caller will ALWAYS be 50000
			--this logs 2 rows to MD3.Log (I, then E)
			--Note that the CATCH block needs to "rethrow" the error and handle whatever it needs to, just like standard TSQL error handling.  
		BEGIN TRY
			EXEC MD3.ExecuteSQL 
				@SQL = 'SELECT * FROM DoesNotExist';
		END TRY 
		BEGIN CATCH 
			--you only need to declare the ERROR variables you will use
			DECLARE @ErrorNumber INT, @ErrorMessage VARCHAR(MAX) 
			EXEC MD3.LogErrorParse 
				@ErrorNumber = @ErrorNumber OUTPUT,
				@ErrorMessage = @ErrorMessage OUTPUT

			--do error handling here
			IF @ErrorNumber = 208
				RAISERROR ('We caught the Error 208 and have chosen to rethrow it (we could just ignore it too)',16,1)
		END CATCH 

		You can see all of this in acton in MD3.CreateCheckConstraint

	*/


	BEGIN TRY
		EXEC [MD3].[InsertLogMessage]
			@Message = @SQL,
			@Severity = 'I',
			@ProcedureName = NULL,
			@ScriptName = NULL;

		EXEC (@SQL);
	END TRY
	BEGIN CATCH
		DECLARE @ErrorSeverity INT
			,@ErrorState INT
			,@ErrorLine INT
			,@ErrorNumber INT
			,@ErrorMessage NVARCHAR(MAX);

		SELECT @ErrorNumber = ERROR_NUMBER()
			,@ErrorMessage = N'ERROR_MESSAGE(): ' + ERROR_MESSAGE() + ' ERROR_NUMBER(): ' + CONVERT(VARCHAR(50),ERROR_NUMBER()) + ' ERROR_SEVERITY(): ' + 
				CONVERT(VARCHAR(50),ERROR_SEVERITY()) + ' ERROR_STATE(): ' + 
				CONVERT(VARCHAR(100),ERROR_STATE()) + ' ERROR_LINE(): ' + CONVERT(VARCHAR(50),ERROR_LINE())
			,@ErrorSeverity = ERROR_SEVERITY()
			,@ErrorState = ERROR_STATE()
			,@ErrorLine = ERROR_LINE()


		EXEC [MD3].[InsertLogMessage]
			@Message = @ErrorMessage,
			@Severity = 'E',
			@ProcedureName = 'MD3.ExecuteSQL',
			@ScriptName = 'See previous MD3.Log entry for failure SQL statement.',
			@ErrorNumber  = @ErrorNumber  ,
			@ErrorMessage  = @ErrorMessage,
			@ErrorLine		 = @ErrorLine,
			@ErrorState		 = @ErrorState,
			@ErrorSeverity	 = @ErrorSeverity;

		RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState, @ErrorNumber, @ErrorLine)

	END CATCH 



END;
GO